//连接数据库方法
const mysql = require("mysql2/promise.js");
const { database } = require("../config/index.js");

//创建连接池
const pool = mysql.createPool(
    {
        host : database.HOST,
        database : database.DATABASE,
        user : database.USERNAME,
        password : database.PASSWORD,
        port : database.PORT,
    }
);

//创建连接池的连接

//执行操作
const allServices = {
  async query(sql) {
    try {
      //获取连接
      const conn = await pool.getConnection();
      const [rows, err] = await conn.query(sql);
      //释放连接
      pool.releaseConnection(conn);
      //返回结果 后面query可以接.then
      return Promise.resolve(rows);
    } catch (error) { 
      return Promise.reject(error); 
    }
  },
};


//登录接口
const userLogin = (username,password) => {
    const _sql = `select * from user_info where username="${username}" and password="${password}";`  
    return allServices.query(_sql);
}

//注册接口
const userRegister = (values) => {
  const _sql = `insert into user_info (username, password, nickname) values ('${values.username}', '${values.password}', '${values.nickname}');`
  return allServices.query(_sql);
}

//根据分类查找数据
const findNoteListByType = (note_type,id) =>{
  const _sql = `select * from note where note_type="${note_type}" and userId = "${id}" ;`
  return allServices.query(_sql);
}

const userQuery = (username) => {
  const _sql = `select username from user_info where username="${username}";`
  return allServices.query(_sql);
}

const findNoteListById = (id) =>{
  const _sql = `select * from note where id="${id}" ;`
  return allServices.query(_sql);
}

//发布笔记
const notePublish = (values) => {
  const _sql = `insert into note (
      title, 
      note_type, 
      note_content, 
      head_img, 
      c_time, 
      m_time, 
      userId, 
      nickname) values("${values.title}", "${values.note_type}", "${values.note_content}", "${values.head_img}", "${values.c_time}", "${values.m_time}", "${values.userId}", "${values.nickname}");`
  
  return allServices.query(_sql)
}

module.exports = {
    userLogin,
    userQuery,
    userRegister,
    findNoteListByType,
    findNoteListById,
    notePublish
}

